﻿CREATE PROCEDURE [dbo].[RefillTimeDim]
	@StartTime datetime = '1990-jan-01',
	@EndTime datetime = '2014-jan-01'
AS

	DECLARE @MonthNames TABLE ( month INT PRIMARY KEY, FullName nvarchar(20), ShortName nvarchar(10) )
	INSERT INTO @MonthNames VALUES
	( 1, 'January', 'jan' ),
	( 2, 'February', 'feb' ),
	( 3, 'March', 'mar' ),
	( 4, 'April', 'apr' ),
	( 5, 'May', 'may' ),
	( 6, 'June', 'jun' ),
	( 7, 'July', 'jul' ),
	( 8, 'August', 'aug' ),
	( 9, 'Septemnet', 'sep' ),
	( 10, 'October', 'oct' ),
	( 11, 'November', 'nov' ),
	( 12, 'December', 'dec' );

	IF (EXISTS(SELECT * FROM sys.objects WHERE [type] = 'U' AND [name] = 'tmp_TimeDim')) DROP TABLE [tmp_TimeDim]
	CREATE TABLE [dbo].[tmp_TimeDim]
	(
		[Time] DATETIME NOT NULL, 
		[Year] SMALLINT NOT NULL, 
		[Month] INT NOT NULL, 
		[MonthOfYear] SMALLINT NOT NULL, 
		[Day] INT NOT NULL, 
		[DayOfMonth] SMALLINT NOT NULL, 
		[Hour] INT NOT NULL, 
		[Minute] BIGINT NOT NULL PRIMARY KEY, 
		[MonthName] NVARCHAR(50) NOT NULL, 
		[MonthOfYearName] NVARCHAR(50) NOT NULL, 
		[DayName] NVARCHAR(50) NOT NULL, 
		[HourName] NVARCHAR(50) NOT NULL, 
		[MinuteName] NVARCHAR(50) NOT NULL
	)



	;WITH DateIterates(dtime) AS
	(
		SELECT @StartTime AS [dtime]
		UNION ALL 
		SELECT DATEADD(minute, 15, dtime) AS [dtime] FROM [DateIterates] WHERE [dtime] < @EndTime
	), DateParts ([DateTime], [Year], [MonthOfYear], [DayOfMonth], [Hour], [Minute], [MonthFull]) AS
	(
		SELECT 
			[DateTime] = d.[dtime],
			[Year] = DATEPART(year, d.[dtime]),
			[MonthOfYear] = DATEPART(month, d.[dtime]),
			[DayOfMonth] = DATEPART(day, d.[dtime]),
			[Hour] = DATEPART(hour, d.[dtime]),
			[Minute] = DATEPART(minute, d.[dtime]),
			[MonthFull] = mname.[FullName]
		FROM DateIterates d 
		INNER JOIN @MonthNames mname ON
			mname.[month] = DATEPART(month, d.[dtime])
	)
	INSERT INTO [tmp_TimeDim] 
		([Time], [Year], [Month], [MonthOfYear], [Day], [DayOfMonth], [Hour], [Minute], [MonthName], [MonthOfYearName], [DayName], [HourName], [MinuteName])
	SELECT
		[Time] = [DateTime],
		[Year] = [Year],
		[Month] = [Year] * 100 + [MonthOfYear],
		[MonthOfYear] = [MonthOfYear],
		[Day] = [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth],
		[DayOfMonth] = [DayOfMonth],
		[Hour] = [Year] * 1000000 + [MonthOfYear] * 10000 + [DayOfMonth] * 100 + [Hour],
		[Minute] = CAST([Year] as bigint) * 100000000 + [MonthOfYear] * 1000000 + [DayOfMonth] * 10000 + [Hour] * 100 + [Minute],
		[MonthName] = CAST([Year] AS nvarchar(4)) + ' ' + [MonthFull],
		[MonthOfYearName] = [MonthFull],
		[DayName] = CONVERT(nvarchar, [DateTime], 111),
		[HourName] = CONVERT(nvarchar, [DateTime], 111) + ' ' + RIGHT(CAST(100 + [Hour] AS nvarchar), 2),
		[MinuteName] = CONVERT(nvarchar, [DateTime], 111) + ' ' + RIGHT(CAST(10000 + [Hour] * 100 + [Minute] AS nvarchar), 4)
	FROM DateParts
	OPTION (MAXRECURSION 0)

	--DROP AND RECREATE THE CONSTRAINT IF NECESSARY
	ALTER TABLE [FloatDataFact] DROP CONSTRAINT [FK_FloatDataFact_ToTime]
	DROP TABLE [TimeDim]
	exec sp_rename 'tmp_TimeDim', 'TimeDim'
	ALTER TABLE [FloatDataFact] ADD CONSTRAINT [FK_FloatDataFact_ToTime]
		FOREIGN KEY ([Time]) REFERENCES [TimeDim]([Minute])

RETURN 0
